package com.yql.commonextend.utils;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import com.yql.common.utils.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 导出excel工具类
 *
 * @author ldr
 * 2017-04-20
 */
public class ExcelUtil {
    /**
     * @param list   数据源
     * @param keys   实体类字段  state
     * @param titles 标题/列名  状态
     * @param zclass 实体类的Class  数据源的实体类.Class
     * @param map    需要装换的字段    hashMap.put("state","1=失败,2=成功");   hashMap.put("state","#item=*100"); 表示*100
     * @return Workbook
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    public static Workbook createWorkBook(List list, List<String> keys, List<String> titles, Class zclass, Map<String, String> map) throws NoSuchFieldException, IllegalAccessException {
        return createWorkBook(list, keys, titles, null, zclass, map);
    }

    /**
     * @param list    实体数据
     * @param keys    需要的字段 [state,name]
     * @param titles  标题     [状态,姓名]
     * @param ctitles 存放 json 的字段  需要特殊处理的json [{name:标题1,val:具体数值 },{name:标题2,val:具体数值 }]
     * @param zclass  实体类  数据源的实体类.Class
     * @param map     需要装换的  hashMap.put("state","1=失败,2=成功");
     * @return
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    public static Workbook createWorkBook(List list, List<String> keys, List<String> titles, String ctitles, Class zclass, Map<String, String> map) throws NoSuchFieldException, IllegalAccessException {
        Workbook wb = new HSSFWorkbook();
        // 创建excel工作簿
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet("sheetName");

        int titlesLength = titles.size();
        //添加Json内的标题
        if (!StringUtils.isBlank(ctitles)) {
            Field field = zclass.getDeclaredField(ctitles);
            field.setAccessible(true);
            Object o = field.get(list.get(0));
            JSONArray jsonArray = new JSONArray(o);
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject object = jsonArray.getJSONObject(i);
                titles.add(object.getStr("name"));
            }

        }
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < titles.size(); i++) {
            sheet.setColumnWidth( i, (int) (35.7 * 150));
        }
        Map<String, Map> m = new HashMap();
        map.keySet().forEach(item -> {
            String str = map.get(item);
            String[] items = str.split(",");
            Map h = new HashMap();
            for (String s : items) {
                String[] k_v = s.split("=");
                h.put(k_v[0], k_v[1]);
            }
            m.put(item, h);
        });
        // 创建第一行
        Row row = sheet.createRow( 0);
        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();
        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());
        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setFillBackgroundColor((short) 22);
        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        // 设置列名
        for (int i = 0; i < titles.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(titles.get(i));
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        for (int i = 0; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow( i + 1);
            // 在row行上创建一个方格

            for (int j = 0; j < keys.size() + 1; j++) {

                if (j < keys.size()) {

                    Field field = zclass.getDeclaredField(keys.get(j));
                    field.setAccessible(true);
                    Object o = field.get(list.get(i));
                    Cell cell = row1.createCell(j);
                    if (m.get(keys.get(j)) != null) { //需要装换的 比如  0=失败 1=成功
                        Map values = m.get(keys.get(j));
                        if (values.get("#operator") != null) { // 计算
                            String value = (String) values.get("#operator");
                            String operator = value.substring(0, 1);
                            String num = value.substring(1);
                            if(o==null){
                                o="0";
                            }
                            switch (operator) {
                                case "*":
                                    cell.setCellValue(new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP)
                                            .multiply(new BigDecimal(num)).toString());
                                    break;
                                case "/":
                                    cell.setCellValue(new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP)
                                            .divide(new BigDecimal(num), 2, RoundingMode.HALF_DOWN).toString());
                                    break;
                                case "+":
                                    cell.setCellValue(new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP)
                                            .add(new BigDecimal(num)).toString());
                                    break;
                                case "-":
                                    cell.setCellValue(new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).
                                            subtract(new BigDecimal(num)).toString());
                                    break;
                            }
                        } else {
                            String value = (String) values.get(o.toString());
                            if (value != null) {
                                cell.setCellValue(value + "");
                            } else {
                                if (o != null) {
                                    cell.setCellValue(o + "");
                                } else {
                                    cell.setCellValue("");
                                }
                            }
                        }


                    } else {
                        if (o != null) {
                            cell.setCellValue(o + "");
                        } else {
                            cell.setCellValue("");
                        }
                    }

                    cell.setCellStyle(cs2);


                } else if (!StringUtils.isBlank(ctitles)) {  // JSON
                    Field field = zclass.getDeclaredField(ctitles);
                    field.setAccessible(true);
                    Object o = field.get(list.get(i));
                    JSONArray jsonArray = new JSONArray(o);
                    for (int b = 0; b < jsonArray.size(); b++) {
                        Cell cell = row1.createCell(j + b);
                        JSONObject object = jsonArray.getJSONObject(b);
                        cell.setCellValue(object.getStr("val"));

                    }
                }

            }
        }
        return wb;
    }


    /**
     * @param list 数据源
     * @return
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    public static Workbook createWorkBookJson(List<String> list) throws NoSuchFieldException, IllegalAccessException {
        Workbook wb = new HSSFWorkbook();
        JSONArray jsonArray = new JSONArray(list.get(0));
        // 创建excel工作簿
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet("sheetName");
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < jsonArray.size(); i++) {
            sheet.setColumnWidth((int) i, (int) (35.7 * 150));
        }
        // 创建第一行
        Row row = sheet.createRow((int) 0);
        class Data {
            String name;
            String var;
        }
        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();
        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        cs.setFont(f);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setFillBackgroundColor((short) 22);
        CellStyle cs2 = wb.createCellStyle();
        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());
        // 设置第一种单元格的样式（用于列名）
        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        // 设置列名
        AtomicInteger ti = new AtomicInteger();
        jsonArray.forEach(item -> {
            Cell cell = row.createCell(ti.getAndIncrement());
            cell.setCellValue(((JSONObject) item).getStr("name"));
            cell.setCellStyle(cs);
        });
        //设置每行每列的值
        for (int i = 0; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow( i + 1);
            JSONArray datas = new JSONArray(list.get(0));
            // 在row行上创建一个方格
            for (int j = 0; j < datas.size(); j++) {
                Cell cell = row1.createCell(j);
                JSONObject jsonObject = (JSONObject) datas.get(j);
                cell.setCellValue(jsonObject.getStr("val"));
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }


    //生成流
    public static void exportExcelAfter(ByteArrayOutputStream os, HttpServletResponse response, String fileName)
            throws Exception {
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (final IOException e) {
            throw e;
        } finally {
            if (bis != null) {
                bis.close();
            }
            if (bos != null) {
                bos.close();
            }
        }
    }

    /**
     * 处理上传的文件
     *
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public static List getBankListByExcel(InputStream in, String fileName) throws Exception {
        List list = new ArrayList();
        //创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }

                List<Object> li = new ArrayList<>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(cell);
                }
                list.add(li);
            }
        }
        return list;
    }

    /**
     * 判断是否是excel
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return workbook;
    }



}